﻿CREATE PROCEDURE [dbo].[proc_Order_Getlist_AnalyseFinish]
(
	@CompanyId int,
	@DepId int,
	@StateId int,
	@TypeId int,
	@WayId int,
	@ProcessId int,
	@Stext nvarchar(50),
	@StartDate varchar(10),
	@EndDate varchar(10),
	@StartIndex int,
	@EndIndex int,
	@NOE_Flag int
)
AS
Begin
Declare @cSql nvarchar(3000),@cWhere1 nvarchar(500)

Set @cSql = ''
Set @cWhere1 = 'CreateDate>=@sRq And CreateDate<=@eRq'

If @CompanyId<>0
Begin
	Set @cSql = 'CompanyId='+Convert(varchar(4),@CompanyId)
	Set @cWhere1 = @cWhere1 + ' And CompanyId='+Convert(varchar(4),@CompanyId)
End
Else
	Set @cSql = 'CompanyId>0'

If @DepId<>0
	Set @cSql = @cSql + ' And DepId In(Select Id From department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T)'

If @StateId<>0
	Set @cSql = @cSql + ' And StateId='+Convert(varchar(10),@StateId)
Else
	Set @cSql = @cSql + ' And StateId>2'

If @TypeId<>0
	Set @cSql = @cSql + ' And TypeId='+Convert(varchar(10),@TypeId)

If @WayId<>0
	Set @cSql = @cSql + ' And WayId='+Convert(varchar(10),@WayId)

If @ProcessId<>0
	Set @cSql = @cSql + ' And ProcessId='+Convert(varchar(10),@ProcessId)

If @Stext<>''
Begin
	Set @cSql = @cSql + ' And (CusName like ''%'+@Stext+'%'' Or Salesman like ''%'+@Stext+'%'')'
	Set @cWhere1 = @cWhere1 + ' And (CusName like ''%'+@Stext+'%'' Or Salesman like ''%'+@Stext+'%'')'
End

If @NOE_Flag<>100
Begin
	Set @cSql = @cSql + ' And NOE_Flag=' + Convert(varchar(10),@NOE_Flag)
	Set @cWhere1 = @cWhere1 + ' And NOE_Flag=' + Convert(varchar(10),@NOE_Flag)
End
--By Zht 2014-05-09 去掉非企限制，对项目分析人员可见
--Else
--Begin
--	Set @cSql = @cSql + ' And NOE_Flag<>1'
--	Set @cWhere1 = @cWhere1 + ' And NOE_Flag<>1'
--End

Set @cSql='Declare @sRq Datetime,@eRq Datetime
Set @sRq = Convert(Datetime,''' + @StartDate + ' 00:00:00'')
Set @eRq = Convert(Datetime,''' + @EndDate + ' 23:59:59'')

Declare @bmbh_T varchar(30)
Select @bmbh_T=bmbh From department Where id=' + Convert(varchar(10),@DepId) + '

;WITH List1 As (
	Select Top 1000 OrderId As OId,Min(CreateDate) As AnalyseDate
	From Project
	Where ' + @cWhere1 + '
	Group By OrderId
	Order By AnalyseDate Desc
),
List2 As (
	Select ROW_NUMBER() OVER (Order By List1.AnalyseDate Desc) As Row
		,Id,o.OId
		,IsNull(OrderId,'''') As OrderId
		,CusId
		,CusName
		,CusPerson
		,CusPersonTel
		,CusDescription
		,CusEstimate
		,TypeId
		,TypeName
		,WayId
		,WayName
		,ProcessId
		,ProcessName
		,Salesman
		,SalesmanAccount
		,TradeDate
		,CreateDate
		,IsNull(YingShouPrice,0) as YingShouPrice
		,IsNull(ShiShouPrice,0) as ShiShouPrice
		,Discount
		,PaymentTypeId
		,PaymentType
		,BankId
		,Bank
		,PaymentDescription
		,OtherDescription
		,DepId
		,CompanyId
		,StateId
		,IsGreen
		,NOE_Flag
		,List1.AnalyseDate
		--,Isnull((select Pizhu from Customer_PiZhu where CustomerId=CusId),'''') as Pizhu --批注内容字段 2015.08.20 
		--,Isnull((select CusState from Customer_PiZhu where CustomerId=CusId),0) as CusState --是否有批注字段 
	From [Order] o Right Join List1 On o.OId=List1.OId
	Where ' + @cSql + '
)

Select *,(Select Count(0) From List2) As RecordCount
,IsNull((Select Title From Order_State Where Id=List2.StateId),''...'') As StateName
From List2
Where Row>='+Convert(varchar(10),@StartIndex)+' And Row <='+Convert(varchar(10),@EndIndex)+'
Order By Row'
--Select @cSql
Execute(@cSql)
--	Declare @sRq Datetime,@eRq Datetime
--	Set @sRq = Convert(Datetime,@StartDate+' 00:00:00')
--	Set @eRq = Convert(Datetime,@EndDate+' 23:59:59')
--
--	Declare @bmbh_T  varchar(30)
--	Select @bmbh_T=bmbh From department Where id=@DepId
--
--	;WITH list As(Select ROW_NUMBER() OVER (ORDER BY TradeDate DESC,Id DESC)AS Row
--		,Id
--		,OId
--		,isnull(OrderId,0) as OrderId
--		,CusId
--		,CusName
--		,CusPerson
--		,CusPersonTel
--		,CusDescription
--		,CusEstimate
--		,TypeId
--		,TypeName
--		,WayId
--		,WayName
--		,ProcessId
--		,ProcessName
--		,Salesman
--		,SalesmanAccount
--		,TradeDate
--		,CreateDate
--		,isnull(YingShouPrice,0) as YingShouPrice
--		,isnull(ShiShouPrice,0) as ShiShouPrice
--		,Discount
--		,PaymentTypeId
--		,PaymentType
--		,BankId
--		,Bank
--		,PaymentDescription
--		,OtherDescription
--		,DepId
--		,CompanyId
--		,StateId
--		,IsGreen
--		,NOE_Flag
--		,IsNULL((Select Title From Order_State Where Id=[Order].StateId),'...') As StateName
--		From [Order]
--		Where (@CompanyId=0 Or CompanyId=@CompanyId) And (@DepId=0 Or DepId In(Select Id From department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T))
--		And CreateDate>=@sRq And CreateDate<=@eRq
--		And ((@StateId=0 And StateId>2) Or StateId=@StateId) And (@TypeId=0 Or TypeId=@TypeId)
--		And (@WayId=0 Or WayId=@WayId) And (@ProcessId=0 Or ProcessId=@ProcessId)
--		And ((@NOE_Flag=100 And NOE_Flag<>1) Or NOE_Flag=@NOE_Flag)
--		And (@Stext='' Or CusName like '%'+@Stext+'%' Or Salesman like '%'+@Stext+'%')
--	)
--
--	Select *,(Select Count(0) From list) As RecordCount From list Where Row Between @StartIndex and @EndIndex Order By row
End
